const db = require("../config/mysql");
const bcrypt = require("bcrypt");

class UsersModel {
  // 根据用户 ID 查询用户信息（用于实时检查用户状态和获取最新角色）
  async findUserById(userId) {
    let sql =
      "SELECT user_id, user_name, role_id, status, created_at FROM user WHERE user_id = ?";
    const [result] = await this.db.query(sql, [userId]);
    // 返回单个用户对象或 undefined
    return result[0];
  }
  constructor() {
    this.db = db;
  }
  // // 用户列表查询
  // async getUsersList() {
  //   let sql = "SELECT * FROM user";
  //   let [result] = await this.db.query(sql);
  //   return result;
  // }

  /**
* 用户列表 + 分页 + 模糊 + 状态过滤
* @param {Object} { page=1, size=10, keyword='', status='' }
* @returns {Object} { rows, total }
*/
  async getUsersPage({ page = 1, size = 10, keyword = '', status = '', role_id = '' } = {}) {
    const offset = (page - 1) * size;

    let where = 'WHERE 1=1';
    const params = [];

    // 模糊查询用户名
    if (keyword) {
      where += ' AND user_name LIKE ?';
      params.push(`%${keyword}%`);
    }

    // 状态过滤
    if (status !== '') {
      where += ' AND status = ?';
      params.push(Number(status));
    }

    // 角色ID过滤
    if (role_id !== '') {
      where += ' AND role_id = ?';
      params.push(Number(role_id));
    }

    // 分页数据 - 修改排序方式为升序
    const [rows] = await this.db.query(
      `SELECT user_id, user_name, role_id, phone, email, status, created_at, updated_at, login, notes
   FROM user
   ${where}
   ORDER BY user_id ASC
   LIMIT ?, ?`,
      [...params, offset, Number(size)]
    );

    // 总条数
    const [totalRow] = await this.db.query(
      `SELECT COUNT(*) AS total FROM user ${where}`,
      params
    );
    const total = totalRow[0].total;

    return { rows, total };
  }

  // 根据用户名查询用户
  async getUserByName(user_name) {
    const [rows] = await this.db.query(
      "SELECT * FROM user WHERE user_name = ?",
      [user_name]
    );
    return rows[0]; // 找到返回用户对象，没找到返回 undefined
  }

  //   添加用户
  async insertUser(userData) {
  // 实际插入逻辑
  const sql = `
    INSERT INTO user (user_name, password, role_id, phone, email, status, notes)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `;
  const [result] = await this.db.query(sql, [
    userData.user_name,
    userData.password,
    userData.role_id,
    userData.phone,
    userData.email,
    userData.status,
    userData.notes
  ]);
  return result.insertId;
}
  // 修改用户
  async updateUser(user_id, updateData) {
    // 构建动态SQL查询
    const fields = [];
    const values = [];

    // 只处理传入的字段
    Object.keys(updateData).forEach(key => {
      // 确保字段是允许更新的
      if (['user_name', 'role_id', 'phone', 'email', 'status', 'notes'].includes(key)) {
        fields.push(`${key} = ?`);
        values.push(updateData[key]);
      }
    });

    // 如果没有要更新的字段，则直接返回
    if (fields.length === 0) {
      return 0;
    }

    // 添加更新时间
    fields.push('updated_at = NOW()');

    // 添加用户ID到values数组的末尾
    values.push(user_id);

    // 执行更新用户信息的SQL查询
    const [res] = await this.db.query(
      `UPDATE user SET ${fields.join(', ')} WHERE user_id = ?`,
      values
    );

    return res.affectedRows;
  }

  // 软删除用户：将 status 修改为 -1
  async deleteUser(user_id) {
    const [res] = await this.db.query(
      `UPDATE user 
     SET status = -1, 
         updated_at = NOW() 
     WHERE user_id = ?`,
      [user_id]
    );
    return res.affectedRows;
  }

  // 登录检查用户是否存在
  async checkUser(username) {
    let sql = "SELECT * FROM user WHERE user_name = ?";
    let [result] = await this.db.query(sql, [username]);
    return result;
  }

  // 修改密码检查旧密码是否匹配
  async checkOldPassword(user_id, old_password) {
    // 1. 查询用户当前密码哈希
    const [rows] = await this.db.query(
      "SELECT password FROM user WHERE user_id = ?",
      [user_id]
    );
    if (!rows || rows.length === 0) {
      throw new Error("用户不存在");
    }
    const storedHash = rows[0].password;
    // 2. 比对旧密码
    const isMatch = await bcrypt.compare(old_password, storedHash);
    return isMatch; // 返回是否匹配
  }



  // 验证通过开始修改
  async updatePassword(user_id, new_password) {
    // console.log(user_id, old_password, new_password);
    try {
      // 3. 对新密码进行哈希处理
      const hashedNewPassword = await bcrypt.hash(new_password, 10);

      // 4. 更新数据库密码
      const [res] = await this.db.query(
        "UPDATE user SET password = ?, updated_at = NOW() WHERE user_id = ?",
        [hashedNewPassword, user_id]
      );
      return res.affectedRows; // 成功返回1，失败返回0
    } catch (err) {
      console.error("更新密码出错:", err);
      throw err;
    }
  }

  //重置密码
  async resetPassword(user_id, new_password) {
    const sql = `update user set password = ? where user_id = ?`
    const [res] = await this.db.query(sql, [new_password, user_id]);
    return res.affectedRows;
  }

  //
/*
* @desc 根据角色id获取用户列表
* @params roleIds 角色ID列表，多个用逗号分隔
*/
  async getUsersByRoleId(role_id) {
    const fields = [
        'user_id',
        'user_name',
        'real_name',
        // 'phone',
        // 'email',
        // 'status',
        // 'login',
        // 'notes',
        // 'created_at',
        // 'updated_at'
    ]
    const fieldStr = fields.join(',');

    const sql = `select ${fieldStr} from user where role_id in (?) and status = 1`;
    const [res] = await this.db.query(sql, [role_id]);
    // 把结果拼成label、value
    const arr = res.map(item => ({
        "label":item.real_name||item.user_name,
        "value":item.user_id
    }))
    return arr;
  }
}

module.exports = UsersModel;
